<?php
/* -------- database settings -------- */
$dbServer = "localhost";
$dbName = "allprices-md";
$dbUser = "root";
$dbPassword = "";

/** this affects cookies (set to false, unless on localhost) */
if (!defined('IS_DEVELOPMENT_SERVER')) define('IS_DEVELOPMENT_SERVER', true);
/** usually just a slash */
if (!defined('BASE_FOLDER')) define('BASE_FOLDER', '/');

/* -------- db logging -------- */
define('LOG_DB_ERRORS', true);
define('LOG_DB_ERRORS_BACKTRACE', true);
define("DEBUG_DB_QUERIES", false);
define('DEBUG_APPLICATION', true);

// Initiate connection
$dbCon = @DbConnect($dbServer, $dbName, $dbUser, $dbPassword);


// Connect to DB and return the connection.
// Returns NULL on failure.
function DbConnect($server, $name, $user, $password)
{
  $con = @mysql_connect($server, $user, $password);
  $db = @mysql_select_db($name, $con);
  if(!$con || !$db)
    return NULL;
  else
  {
    mysql_query("SET NAMES 'utf8'", $con);
    return $con;
  }
}

// Disconnect form DB.
function DbDisconnect($server, $name, $user, $password)
{
  $res = mysql_close($server, $user, $password);
  if(!$res)
    return false;
  else
    return true;
}

/**
 * Checks that the connection is alive, and if not, creates a new one.
 * @global <type> $dbServer
 * @global <type> $dbName
 * @global <type> $dbUser
 * @global <type> $dbPassword
 * @global <type> $dbCon
 * @param boolean $reconnect - TRUE to reconnect in case that the connection is down.
 * @return boolean - TRUE if a connection is live (either the old connection that
 *                   was live, or the newly created one).
 */
function DbCheckConnection($reconnect = false)
{
  global $dbServer, $dbName, $dbUser, $dbPassword, $dbCon;
  $alive = @mysql_ping($dbCon);
  if (!$alive && $reconnect === true)
  {
    @DbDisconnect($dbServer, $dbName, $dbUser, $dbPassword);
    $dbCon = @DbConnect($dbServer, $dbName, $dbUser, $dbPassword);
    $alive = (bool) $dbCon;
  }
  return $alive;
}


// Executes a given query and returns the rows 
// as an array of associative arrays of the form (column -> value)
function &ExecuteQuery($query)
{
  $timer = StartDebugTimer();
  global $dbCon;
  $result = array();
  $rows = @mysql_query($query, $dbCon);
  
//  if ($rows === FALSE && LOG_DB_ERRORS)
//    LogDbErrorToFile($query);
  
  $numFields = mysql_num_fields($rows);
  $rowNum = 0;
  while ($row = mysql_fetch_array($rows))
  {
    for ($i = 0; $i < $numFields; $i++)
    {
      $fieldName = mysql_field_name($rows, $i);
      $result[$rowNum][$fieldName] = $row[$i];
    }
    $rowNum++;
  }
  DebugQuery($query, $timer, $numFields);
  return $result;
}

/**
 * Executes a "paginated" SELECT query by adding LIMIT clause and counting the
 * total number of rows (see $paginationData parameter)
 * Note: your query must contain a global SELECT query with no LIMIT clauses
 *      (you cant apply pagination something like SELECT {..} UNION SELECT {...})
 * @param <type> $query
 * @param <type> $paginationData - array that stores pagination data
 *  array (
 *    'from' - row number to limit
 *    'to' - row number to limit
 *    'total' -  total number of rows found
 * )
 * @return <Array> - query result
 *
 * Usage:
 * $paginationData = array('from' => 0, 'to' => 1);
 * $rows = ExecutePaginatedQuery("SELECT * FROM foo", $paginationData);
 * var_dump($rows) // will output one row
 * var_dump($paginationData['total']) - will output total number of rows in foo table
 */
function &ExecutePaginatedQuery(&$query, &$paginationData = array())
{
  $from = intval($paginationData['from']);
  $to = intval($paginationData['to']);
  $count = $to - $from;
  $limit = '';
  if (intval($count) > 0)
    $limit = " LIMIT $from, $count";
  else if ($from > 0)
    $limit = " LIMIT $from, 18446744073709551615"; // To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. (mysql manual)

  $query = trim($query);
  // if query starts with SELECT statement we can insert SQL_CALC_FOUND_ROWS prefix.s
  if (stripos($query, "SELECT ") === 0)
  {
    $query = substr($query, 7);
    // as stated in mysql 5 manual, the best way to find total number of rows is
    // to add SQL_CALC_FOUND_ROWS prefix
    $result = ExecuteQuery("SELECT SQL_CALC_FOUND_ROWS $query $limit");

    // get the total number of rows in a separate query.
    $rows = ExecuteQuery("SELECT FOUND_ROWS() AS total");
    $paginationData['total'] = $rows[0]['total'];
  }
  else
    // otherwise dont count total number of rows
    $result = ExecuteQuery("$query $limit");

  return $result;
}


// Executes a query that returns no data (e.g. UPDATE, INSERT).
// Return true on success, and false on failure.
function ExecuteNonQuery($query)
{
  $timer = StartDebugTimer();
  global $dbCon;
  $res = mysql_query($query, $dbCon);
  if ($res === FALSE)
    if (LOG_DB_ERRORS)
      LogDbErrorToFile($query);
  DebugQuery($query, $timer);
  if ($res)
    return true;
  else
    return false;
}

function SelectRow($table, $columns, $where=1, $suffix='')
{
  if(!isset($where)) $where = 1;
  $query = "SELECT $columns FROM `$table` WHERE $where $suffix";
  return ExecuteQuery($query); 
}

function SearchRows($table, $columns, $match, $against, $where=1)
{
  if(!isset($where)) $where = 1;
  if ($against)
    $query = "SELECT $columns FROM `$table` WHERE $where AND MATCH($match) AGAINST(\"$against\" IN BOOLEAN MODE)";
  else 
    $query = "SELECT $columns FROM `$table` WHERE $where";
  return ExecuteQuery($query);
}

function GetLastInsertId($table)
{
  $res = ExecuteQuery("SELECT LAST_INSERT_ID() as id FROM `$table`");
  return $res[0]['id'];
}

function FormatSqlString($value)
{
  return mysql_real_escape_string($value);
}
function ToSqlQuotedString($value)
{
  return "'".FormatSqlString($value)."'";
}

function ToSqlDate($timestamp)
{
  return date('Y-m-d', $timestamp);
}
function ToSqlDatetime($timestamp)
{
  return date('Y-m-d H:i:s', $timestamp);
}

function InsertRow($table, $values)
{
  $strColumns = '';
  $strValues = '';
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if(is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);

    $strColumns .= ("$strColumns" != '' ? ',' : '') . "`$column`";
    $strValues .= ($strValues != '' ? ',' : '') . $value;
  }
  $query = "INSERT INTO `$table` ($strColumns) values ($strValues)";
  return ExecuteNonQuery($query);
}

function InsertRows($table, $rowsValues)
{
  $columns = array();
  $strRowsValues = array();
  foreach ($rowsValues as $rowValues)
  {
    $strRowValues = '';
    foreach ($rowValues as $column => $value)
    {
      $column = trim($column);
      if(is_null($value))
        $value = 'NULL';
      else
        $value = ToSqlQuotedString($value);

      $columns[$column] =  "`$column`";
      $strRowValues .= ($strRowValues != '' ? ',' : '') . $value;
    }
    $strRowsValues[] = $strRowValues;
  }
  $query = "INSERT INTO `$table` (" . implode(", ", $columns). ") "
          ." VALUES (" . join("), (", $strRowsValues) . ")";
  return ExecuteNonQuery($query);
}

function ReplaceRow($table, $values)
{
  $strColumns = '';
  $strValues = '';
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if(is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);

    $strColumns .= ($strColumns != '' ? ',' : '') . $column;
    $strValues .= ($strValues != '' ? ',' : '') . $value;
  }
  $query = "REPLACE INTO $table ($strColumns) values ($strValues)";
  return ExecuteNonQuery($query);
}

function UpdateRow($table, $values, $where=1)
{
  $strValues = '';
  if(!isset($where)) $where = 1;
  foreach ($values as $column=>$value)
  {
    $column = trim($column);
    if(is_null($value))
      $value = 'NULL';
    else
      $value = ToSqlQuotedString($value);
      
    $strValues .= ($strValues != '' ? ',' : '') . "`$column` = $value";
  }
  $query = "UPDATE `$table` SET $strValues WHERE $where";
  return ExecuteNonQuery($query);
}

function DeleteRow($table, $where=1)
{
  if(!isset($where)) $where = 1;
  $query = "DELETE FROM `$table` WHERE $where";
  return ExecuteNonQuery($query);
}

function RowExists($table, $where=1)
{
  $timer = StartDebugTimer();
  global $dbCon;
  if(!isset($where)) $where = 1;
  $query = "SELECT * FROM `$table` WHERE $where LIMIT 1";
  $rows = mysql_query($query, $dbCon);
  DebugQuery($query, $timer);
  if (mysql_num_rows($rows) > 0)
    return true;
  else
  {
    return false;
  }
}

function GetTableColumns($tableName)
{
  // get columns names
  $query = "SHOW COLUMNS FROM `$tableName`";
  $rows = ExecuteQuery($query);
  $columns = array();
  foreach ($rows as $row)
    $columns[] = $row['Field'];
  return $columns;
}

function ColumnExists($tableName, $columnName)
{
  $res = false;
  $timer = StartDebugTimer();
  global $dbCon;
  global $dbName;
  $columns  = GetTableColumns($tableName);
  return in_array($columnName, $columns);
}

function TableExists($tableName)
{
  global $dbName;
  $timer = StartDebugTimer();
  $query = "SELECT DISTINCT table_name FROM information_schema.tables "
          . " WHERE table_name=" . ToSqlQuotedString($tableName)
          . " AND table_schema=" . ToSqlQuotedString($dbName);
  $rows = ExecuteQuery($query);
  DebugQuery($query, $timer);
  return (count($rows) > 0 && $rows[0]['table_name'] == $tableName);
}

function RenameTable($oldName, $newName)
{
  $oldName = mysql_real_escape_string($oldName);
  $newName = mysql_real_escape_string($newName);
  $query = " ALTER TABLE `$oldName` RENAME TO `$newName`";
  ExecuteNonQuery($query);
}

function CountRows($table, $where = 1)
{
  $timer = StartDebugTimer();
  // select count(*) is faster than select count(column)
  global $dbCon;
  if(!isset($where)) $where = 1;
  $query = "SELECT COUNT(*) AS count FROM `$table` WHERE $where";
  $rows = mysql_query($query, $dbCon);
  $row = mysql_fetch_array($rows);
  DebugQuery($query, $timer);
  return intval($row['count']);
}

function LockTable($table, $type)
{
  $query = "LOCK TABLES `$table` $type";
  return ExecuteNonQuery($query);
}

function UnlockTables()
{
  return ExecuteNonQuery("UNLOCK TABLES");
}

function BoolToSqlBit($bool)
{
  return $bool===true?1:0;
}

function SqlBitToBool($number)
{
  return ($number === 1 || $number === '1')?true:false;
}

/**
 * Builds SQL IN clause "$column IN ($values[0], $values[1],...)" or "FALSE" if
 *    $values is an empty array.
 */
function BuildSqlInClause($column, $values)
{
  if (count($values) > 0)
  {
    $where = '';
    foreach ($values as $value)
      $where .= ($where == '' ? '' : ', ') . ToSqlQuotedString($value);
    return "$column IN ($where)";
  }
  else
    return " FALSE";
};

function LogDbErrorToFile($query)
{
  $backtrace = debug_backtrace();
  $msg = "MySQL ".mysql_errno(). ': '.mysql_error();
  if ($query !== null)
    $msg .= "\nquery: $query";
  if (LOG_DB_ERRORS_BACKTRACE) {
    foreach ($backtrace as $key => $value) {
      $msg .= "\n[{$key}]File -> ".$value['file'].' (line: '.$value['line'].') function -> '.$value['function'];
    }
  }
}

function StartDebugTimer()
{
  if (DEBUG_DB_QUERIES !== true)
    return;
  return microtime(true);
}

function DebugQuery($query, $timer, $resultsCount = null)
{
  global $DATABASE_EXECUTION_TOTAL_TIME;
  global $PHP_EXECUTION_TOTAL_TIME;
  global $DATABASE_QUERIES;
  $DATABASE_QUERIES = intval($DATABASE_QUERIES) + 1;
  static $lastCall;
  if (DEBUG_DB_QUERIES !== true)
    return;
  $backtrace = debug_backtrace();
  $elapsedQuery = round((microtime(true) - $timer) * 1000, 2);
  $DATABASE_EXECUTION_TOTAL_TIME = intval($DATABASE_EXECUTION_TOTAL_TIME) + $elapsedQuery;
  // log also time from the last DebugQuery call to find possible bottlenecks in
  // the php code besides database queries.
  $strTotal = '';
  if ($lastCall)
  {
    $elapsedLastCall = round((microtime(true) - $lastCall) * 1000, 2);
    $elapsedPhp = $elapsedLastCall - $elapsedQuery;
    $PHP_EXECUTION_TOTAL_TIME = intval($PHP_EXECUTION_TOTAL_TIME) + $elapsedPhp;
    $strTotal = " (php: {$elapsedPhp}ms, dbtotal: {$DATABASE_EXECUTION_TOTAL_TIME}ms, phptotal: {$PHP_EXECUTION_TOTAL_TIME}ms), ";
  }
  $lastCall = microtime(true);
  $msg = "NUM: $DATABASE_QUERIES, TIME: {$elapsedQuery}ms,$strTotal" ;
  $msg .= "QUERY: $query, " ;
  $msg .= "LINE:{$backtrace[1]['file']}:{$backtrace[1]['line']}, " ;
  if ($resultsCount)
    $msg .= "RESULTS: " + ($resultsCount + 1) + " rows" ;
}

/**
* Converts the total number of days the number of days since year 0
* to the timestamp (which is since UNIX epoch). 
* Use in conjunction with mysql TO_DAYS('Y-m-d') function.
* 
* @param int $numberOfDays
* @return int timestamp
*/
function FromDays($numberOfDays)
{
  // 719528 days since year 0 till UNIX epoch
  if (intval($numberOfDays) <= 719528)
    return 0;
  return (intval($numberOfDays) - 719528) * 86400;
}

/**
* Converts timestamp (which is since UNIX epoch)
* to the total number of days since year 0.
* Use in conjunction with mysql FROM_DAYS() function.
* 
* @param int $timestamp
* @return int Number of days the number of days since year 0.
*/
function ToDays($timestamp)
{
  if (intval($timestamp) <= 0)
    return 719528;
  return round($timestamp / 86400) + 719528;
}

?>
